package org.hibernate.cfg.reveng.dialect;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;

import org.hibernate.tool.hbm2x.StringUtils;

/**
 * MySql使用DatabaseMetaData能够反转字段注释，但是不能反转表注释；
 * 
 * 测试版本：mysql5.0；
 * 
 * @author iPan
 * @version 2013-12-14
 */
public class MySQLMetaDataDialect extends JDBCMetaDataDialect {

	private static final String SQL_TABLE_NONE = "select TABLE_NAME, TABLE_SCHEMA AS TABLE_SCHEM"
			+ ", TABLE_CATALOG AS TABLE_CAT, TABLE_TYPE, TABLE_COMMENT AS REMARKS from INFORMATION_SCHEMA.TABLES";

	private static final String SQL_TABLE_SCHEMA = SQL_TABLE_NONE + " where TABLE_SCHEMA = ?";

	private static final String SQL_TABLE_TABLE = SQL_TABLE_NONE + "where TABLE_NAME = ?";

	private static final String SQL_TABLE_SCHEMA_AND_TABLE = SQL_TABLE_NONE + " where TABLE_SCHEMA = ? and TABLE_NAME = ?";

	private PreparedStatement prepTableNone;

	private PreparedStatement prepTableSchema;

	private PreparedStatement prepTableTable;

	private PreparedStatement prepTableSchemaAndTable;

	/**
	 * Based on info from
	 * http://dev.mysql.com/doc/refman/5.0/en/show-table-status.html Should work
	 * on pre-mysql 5 too since it uses the "old" SHOW TABLE command instead of
	 * SELECT from infotable.
	 */
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public Iterator getSuggestedPrimaryKeyStrategyName(String catalog, String schema, String table) {
		String sql = null;
		try {
			catalog = caseForSearch(catalog);
			schema = caseForSearch(schema);
			table = caseForSearch(table);

			log.debug("geSuggestedPrimaryKeyStrategyName(" + catalog + "." + schema + "." + table + ")");

//			sql = "show table status " + (catalog == null ? "" : " from " + catalog + " ") 
//					+ (table == null ? "" : " like '" + table + "' "); // JDBC实现使用
			sql = "show table status" + (schema == null ? "" : " from " + schema + " ") 
					+ (table == null ? "" : " like '" + table + "' "); // SQL实现使用
			PreparedStatement statement = getConnection().prepareStatement(sql);

			final String sc = schema;
			final String cat = catalog;
			return new ResultSetIterator(statement.executeQuery(), getSQLExceptionConverter()) {

				Map element = new HashMap();

				protected Object convertRow(ResultSet tableRs) throws SQLException {
					element.clear();
					element.put("TABLE_NAME", tableRs.getString("NAME"));
					element.put("TABLE_SCHEM", sc);
					element.put("TABLE_CAT", cat);
					String string = tableRs.getString("AUTO_INCREMENT");
					if (string == null) {
						element.put("HIBERNATE_STRATEGY", null);
					} else {
						element.put("HIBERNATE_STRATEGY", "identity");
					}
					return element;
				}

				protected Throwable handleSQLException(SQLException e) {
					// schemaRs and catalogRs are only used for error reporting
					// if
					// we get an exception
					throw getSQLExceptionConverter()
							.convert(
									e,
									"Could not get list of suggested identity strategies from database. Probably a JDBC driver problem. ",
									null);
				}
			};
		} catch (SQLException e) {
			throw getSQLExceptionConverter().convert(e,
					"Could not get list of suggested identity strategies from database. Probably a JDBC driver problem. ",
					sql);
		}
	}

	// DatabaseMetaData默认JDBC实现并不会反转注释，所以需要查询不同数据库数字字典来实现反转；
	@SuppressWarnings({ "unchecked", "rawtypes" })
	@Override
	public Iterator getTables(final String catalog, final String schema, String table) {
		try {
			log.debug("getTables(" + catalog + "." + schema + "." + table + ")");
			ResultSet tableRs;
			tableRs = getTableResultSet(schema, table);

			return new ResultSetIterator(null, tableRs, getSQLExceptionConverter()) {

				Map element = new HashMap();

				protected Object convertRow(ResultSet tableResultSet) throws SQLException {
					element.clear();
					element.put("TABLE_NAME", tableResultSet.getString("TABLE_NAME"));
					element.put("TABLE_SCHEM", tableResultSet.getString("TABLE_SCHEM"));
					element.put("TABLE_CAT", null);
					String tableType = tableResultSet.getString("TABLE_TYPE");
					if ("BASE TABLE".equals(tableType)) {
						tableType = "TABLE";
					}
					element.put("TABLE_TYPE", tableType);
					String remarks = tableResultSet.getString("REMARKS");
					if (StringUtils.isNotEmpty(remarks)) { // 去掉尾部InnoDB free
						int index = remarks.lastIndexOf("; InnoDB free");
						if (index < 0) {
							index = remarks.lastIndexOf("InnoDB free");
						}
						if (index >= 0) {
							remarks = remarks.substring(0, index);
						}
					}
					element.put("REMARKS", remarks);
					log.info(element);
					return element;
				}

				protected Throwable handleSQLException(SQLException e) {
					// schemaRs and catalogRs are only used for error reporting
					// if
					// we get an exception
					String databaseStructure = getDatabaseStructure(catalog, schema);
					throw getSQLExceptionConverter().convert(
							e,
							"Could not get list of tables from database. Probably a JDBC driver problem. "
									+ databaseStructure, null);
				}
			};
		} catch (SQLException e) {
			// schemaRs and catalogRs are only used for error reporting if we
			// get an exception
			String databaseStructure = getDatabaseStructure(catalog, schema);
			throw getSQLExceptionConverter()
					.convert(
							e,
							"Could not get list of tables from database. Probably a JDBC driver problem. "
									+ databaseStructure, null);
		}
	}

	private ResultSet getTableResultSet(final String schema, String table) throws SQLException {
		ResultSet tableRs;
		if (prepTableNone == null) {
			// Prepare table queries
			log.debug("Preparing table queries...");
			Connection connection2 = getConnection();
			prepTableNone = connection2.prepareStatement(SQL_TABLE_NONE);
			prepTableSchema = connection2.prepareStatement(SQL_TABLE_SCHEMA);
			prepTableTable = connection2.prepareStatement(SQL_TABLE_TABLE);
			prepTableSchemaAndTable = connection2.prepareStatement(SQL_TABLE_SCHEMA_AND_TABLE);
			log.debug("  ...table queries prepared!");
		}
		if (schema == null && table == null) { // 00
			tableRs = prepTableNone.executeQuery();
		} else if (schema != null) {
			if (table == null) { // 10
				prepTableSchema.setString(1, schema);
				tableRs = prepTableSchema.executeQuery();
			} else { // 11
				prepTableSchemaAndTable.setString(1, schema);
				prepTableSchemaAndTable.setString(2, table);
				tableRs = prepTableSchemaAndTable.executeQuery();
			}
		} else { // 01
			prepTableTable.setString(1, table);
			tableRs = prepTableTable.executeQuery();
		}
		return tableRs;
	}
	
}
